package com.mes.service.manu;

import com.yy.base.common.dao.Dao;
import com.yy.base.common.service.BaseService;
import com.yy.base.util.StringUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

@Service
public class JobOrderService extends BaseService {
	@Autowired
	private Dao dao;

	/**
	 * 保存排版记录
	 * @param ids
	 * @param productId
	 * @param colorId
	 * @return
	 */
	public String savePaibannLog(String []ids, String productId, String colorId, String orgId, String userId) {
		String uuid = StringUtil.getUUID();
		//验证是否是同一材质、颜色

		String sql = "insert into Emeso_Job_Paiban(Emeso_Job_Paiban_Id, M_Product_ID, Emes_Color_Id, ad_org_id, createdby, updatedby)" +
				"values (?, ?, ?, ?, ?, ?)";
		//添加排版记录
		dao.executeSql(sql, uuid, productId, colorId, orgId, userId, userId);

		String check = "select count(*) from emeso_job_order_material where Emeso_Job_Paiban_Id is not null and emeso_job_order_material_id = ?";
		String mxSql = "update emeso_job_order_material set status = 'PAIBAN', Emeso_Job_Paiban_Id = ? where emeso_job_order_material_id = ?";

		//添加排版明细
		for (int i = 0;i < ids.length;i ++) {
			if (dao.getSqlCount(check, ids[i]) > 0) {
				throw new RuntimeException("选择的辅料中第" + (i + 1) + "条已被排版，请刷新列表后重试！");
			}
			dao.executeSql(mxSql, uuid, ids[i]);
		}
		return uuid;
	}

	/**
	 * 获取采集页面页签信息
	 * @param color
	 * @param product
	 * @param orderCode
	 * @return
	 */
	public List<Map<String, Object>> getJobOrderList(String color, String product, String orderCode) {
		String sql = "select om.emeso_job_order_material_id id, jo.code, om.m_product_id productId, p.value productCode, p.name productName, jo.qty,\n" +
				"\t\t\tom.emes_color_id colorId, c.name COLOR,\n" +
				"\t\t\t (select p.name from emes_shape p where p.emes_shape_id = om.emes_shape_id) SHAPE,\n" +
				"\t\t\t (select p.name from m_product p where p.m_product_id = om.m_product_id) czName,\n" +
				"\t\t\t (select p.value from m_product p where p.m_product_id = om.m_product_id) czCode\n" +
				"  from m_product p, emes_color c, emeso_job_order_material om, emeso_job_order jo\n" +
				" where p.m_product_id = jo.m_product_id\n" +
				"\t and c.emes_color_id = om.emes_color_id\n" +
				"\t and om.emeso_job_paiban_id is null " +
				"\t and om.emeso_job_order_id = jo.emeso_job_order_id";

		List params = new ArrayList();
		if (!StringUtil.isEmpty(color)) {
			sql += " and om.emes_color_id = ?";
			params.add(color);
		}
		if (!StringUtil.isEmpty(product)) {
			sql += " and (p.name like ? or p.value like ?)";
			params.add("%" + product + "%");
			params.add("%" + product + "%");
		}
		if (!StringUtil.isEmpty(orderCode)) {
			sql += " and jo.code like ?";
			params.add("%" + orderCode + "%");
		}
		return dao.query(sql + "  order by jo.exe_date_from", params.toArray());
	}

	/**
	 * 获取排版详细信息
	 * @param id
	 * @return
	 */
	public List<Map<String, Object>> getJobPaibanInfo(String id) {
		String sql = "select om.emeso_job_order_material_id id, jo.code, om.m_product_id productId, p.value || ' - ' || p.name product, jo.qty, \n" +
				"\t\t\tom.emes_color_id colorId, c.code || ' - ' || c.name COLOR,\n" +
				"\t\t\t (select p.code || ' - ' || p.name from emes_shape p where p.emes_shape_id = om.emes_shape_id) SHAPE,\n" +
				"\t\t\t (select p.value || ' - ' || p.name from m_product p where p.m_product_id = om.m_product_id) czName\n" +
				"  from m_product p, emes_color c, emeso_job_order_material om, emeso_job_order jo\n" +
				" where p.m_product_id = jo.m_product_id\n" +
				"\t and c.emes_color_id = om.emes_color_id\n" +
				"\t and om.emeso_job_order_id = jo.emeso_job_order_id" +
				"   and om.Emeso_Job_Paiban_id = ?";
		return dao.query(sql, id);
	}

	/**
	 * 获取排版历史记录
	 * @param orderCode
	 * @param product
	 * @param color
	 * @param time
	 * @return
	 */
	public List<Map<String, Object>> getJobPaibanList(String orderCode, String product, String color, String time) {
		String sql = "select jp.emeso_job_paiban_id id, jp.code pbCode, jo.code, p.value || ' - ' || p.name product, jo.qty, \n" +
				"\t\t\tom.emes_color_id colorId, c.code || ' - ' || c.name COLOR,\n" +
				"\t\t\t (select p.code || ' - ' || p.name from emes_shape p where p.emes_shape_id = om.emes_shape_id) SHAPE,\n" +
				"\t\t\t (select p.value || ' - ' || p.name from m_product p where p.m_product_id = om.m_product_id) czName\n" +
				"  from m_product p, emes_color c, emeso_job_order jo, emeso_job_order_material om, emeso_job_paiban jp \n" +
				" where p.m_product_id = jo.m_product_id\n" +
				"\t and c.emes_color_id = jp.emes_color_id\n" +
				"\t and om.emeso_job_order_id = jo.emeso_job_order_id" +
				"   and om.Emeso_Job_Paiban_id = jp.Emeso_Job_Paiban_id";

		List params = new ArrayList();
		if (!StringUtil.isEmpty(color)) {
			sql += " and om.emes_color_id = ?";
			params.add(color);
		}
		if (!StringUtil.isEmpty(product)) {
			sql += " and (p.name like ? or p.value like ?)";
			params.add("%" + product + "%");
			params.add("%" + product + "%");
		}
		if (!StringUtil.isEmpty(orderCode)) {
			sql += " and jo.code like ?";
			params.add("%" + orderCode + "%");
		}
		return dao.query(sql + "  order by jp.created desc, jp.emeso_job_paiban_id", params.toArray());
	}
	/**
	 * 获取辅料裁断相关所有材质
	 * @return
	 */
	public List<Map<String, Object>> getProductList() {
		String sql = "select p.m_product_id, p.value, p.name from m_product p \n" +
				" where exists (\n" +
				"   select 1 from emes_pbom_item_shape pis, emes_pbom_item item, emes_pbom bom \n" +
				"\t  where pis.emes_pbom_item_id = item.emes_pbom_item_id\n" +
				"\t\t  and item.emes_pbom_id = bom.emes_pbom_id\n" +
				"\t\t  and bom.m_product_id = p.m_product_id\n" +
				")";
		return dao.query(sql);
	}
	/**
	 * 获取辅料裁断相关所有颜色
	 * @return
	 */
	public List<Map<String, Object>> getColorList() {
		String sql = "select c.emes_color_id id, c.code, c.name from emes_color c \n";
		return dao.query(sql);
	}
}